package com.pineapple.module.core.standards.hormone.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.pineapple.module.core.standards.hormone.domain.Hormone;
import com.pineapple.module.core.standards.hormone.domain.HormoneCriteria;
import com.pineapple.module.core.standards.hormone.domain.HormoneSearch;

public class HormoneDAO {

	protected List<HormoneSearch> search(HormoneCriteria criteria,Connection conn) throws Exception{
		List<HormoneSearch> lsthormone = new ArrayList<HormoneSearch>();
		HormoneSearch hormone = null;
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM hormone ");
		sql.append("WHERE 1=1 ");

		if(criteria.getHormoneId()!=null && !criteria.getHormoneId().equals("")){
			sql.append("AND hormone_id  LIKE '"+criteria.getHormoneId()+"%' ");
		}
		if(criteria.getHormoneName()!=null && !criteria.getHormoneName().equals("")){
			sql.append("AND hormone_name LIKE '"+criteria.getHormoneName()+"%' ");
		}


		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
		        while(rs.next()) {
		        	hormone= new HormoneSearch();
		        	hormone.setHormoneId(rs.getString("hormone_id"));
		        	hormone.setHormoneName(rs.getString("hormone_name"));
		        	hormone.setHowToUse(rs.getString("hormone_howToUse"));

		        	lsthormone.add(hormone);
		        }


		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return lsthormone;
	}



	protected void add(Connection conn, Hormone hormone)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO hormone (hormone_name,hormone_howToUse)");
		sql.append("VALUES (");

		sql.append("'"+hormone.getHormoneName()+"' ");
		sql.append(",'"+hormone.getHowToUse()+"' ");
		sql.append(" )");

			System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			 stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}
	protected void edit(Connection conn, Hormone hormone)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE hormone SET ");
		sql.append("hormone_name='"+hormone.getHormoneName()+"' ");
		sql.append(",hormone_howToUse='"+hormone.getHowToUse()+"' ");
		sql.append(" WHERE hormone_id="+hormone.getHormoneId());


		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}

	protected void delete(Connection conn, String hormoneIds)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM hormone  ");

		sql.append(" WHERE hormone_id IN ("+hormoneIds+")");
		System.out.println(sql.toString());
		try(Statement stmt = conn.createStatement()){
			stmt.executeUpdate(sql.toString());
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}


	protected Hormone searchById(Connection conn,int id)throws Exception{

		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * FROM hormone ");
		sql.append("WHERE hormone_id ="+id);
		Hormone hormone = null;
		try(Statement stmt = conn.createStatement()){
			  ResultSet rs = stmt.executeQuery(sql.toString());
			   hormone = new Hormone();
		        if(rs.next()) {
		        	hormone.setHormoneId(rs.getString("hormone_id"));
		        	hormone.setHormoneName(rs.getString("hormone_name"));
		        	hormone.setHowToUse(rs.getString("hormone_howToUse"));

		        }

		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
		return hormone;
	}
}
